#!/bin/bash

#----------------Configuracion--------------#
USER=paquetesystem
PASSWORD=paquete
DATABASE=paquetesystem
S_MAIL=paquetesystem@gmail.com
#----------------Mantenimiento--------------#
cd /root/paquetesystem/txmit/
rm data.mail.temp
rm data.mail.temp2
rm txmit.mail.sh
rm mail.temp
#----------------Ejecucion------------------#
mysql --user=$USER --password=$PASSWORD $DATABASE -B -N  -e "select distinct concat_ws('|',a.trk,a.trk_local,a.date,c.e_mail,b.comentario,a.fecha,a.planta,a.pais,a.status) from DATA a, TRACKING b, USERS c where a.trk = b.trk and c.user_id = b.user_id and c.e_mail <> '' and c.e_mail is not null and a.reg_status = '1'" > data.mail.temp

cat data.mail.temp  | awk 'BEGIN{FS="|"}{ temp=""; if ($2 == "") temp="1111111111111" ;else temp = $2; print $1"|"temp"|"$3"|"$4"|"$5"|"$6"|"$7"|"$8"|"$9}'|sort > data.mail.temp2
cat data.mail.temp2 | awk -v s_mail=$S_MAIL 'BEGIN{FS="|"}{ temp=""; if ($2 == "1111111111111") temp = ""; else temp = $2; print "echo -e \"To: "$4"\\nFrom: "s_mail"\\nSubject: Actualizacion: "$1"-"$5"\\nPlanta -> "$7"\\nPais -> "$8"\\nStatus ->"$9"\\nTracking local -> "temp"\" > mail.temp && cat mail.temp | msmtp -a default "$4}' > txmit.mail.sh

chmod 750 txmit.mail.sh
./txmit.mail.sh

cat data.mail.temp  | awk 'BEGIN{FS="|"}{print "UPDATE DATA SET reg_status = \x27T\x27, date = date where trk = \x27"$1"\x27 and fecha = \x27"$6"\x27 ;"}' > txmit_update.sql
mysql --user=$USER --password=$PASSWORD $DATABASE < txmit_update.sql
